Cement or Drywall matters, but not for the reason you think
You can use cement board or drywall, for a kitchen backsplash. In terms of stiffness it makes no difference. (Bathroom walls, and any tiled floor is a different matter).
That settles it? Sorry, no, there's more to it, and it matters.
Here is a long story, because I have been burnt by conflicting advice. It took me plenty of research and experimentation (and arguments) to get all the intertwined facts straight in what we think is a simple installation.
One backing DOES fail more likely than the other, but its not for the reason you think ...
Have you thought about the effect of the adhesive?
What does matter is how you are attaching the tiles to the backing. There are two adhesion options: tile cement (left) or mastic tile glue (right).

If you are using drywall, beware that mastic tile glue is saturated with water and that moisture will wet the drywall paper, thus weakening it and damaging it in the process of adhesion.
If additionally you have primed or painted the drywall prior to adhesion, the paint or primer may detach from the drywall with the same weakened result.
What are your seams made of?
Tile separation is particularly exasperating if you are tiling over mudded sections of the wall, and the mud you used was general purpose mud, not setting compound. (Setting compound hardens as a chemical process, like cement. It does not dry like pre-mix compound.)
Generally drywall seams are of course taped and smoothed prior to tiling. This is necessary to get stable and even backing for accurate surface-levelling of tiles.
However, the water from the mastic will penetrate through any primer and paint (even if cured), soak the all purpose or topping compound, and separate the paint/primer from it.
The tiles will simply peel off leaving you with a sheet a tiles slowly drooping off the wall over night. What often prevents or obscures such disasters is the grout which holds it all together until the next reno, but that is a job it is not designed for.
This is the cause of loose tiles and cracked grout, and less so the stiffness of drywall sheet vs cement board.
Watch out near windows and corners!
In a kitchen the entire backsplash may be on a single seam-less sheet of drywall with no notable risk of failure. But near corners and windows the tiles may be attached over a copious layer of filler that is lurking behind the primer.
Use Cement Board and/or Tile Cement
With cement board you don't have this problem, since the seams are cemented. The boards and such cement will not fail when re-moisturized by mastic glue.
You also don't have this problem with tile cement on primed or unprimed drywall. Tile cement will use the water in its chemical process before it can soak and damage the underlying paper or compound.
The role of a rubber sealant
Should you already have used drywall and filled with drying-style "pre-mix" compound, you should use tile cement for adhesion.
If tile cement is not an option or not preferred (for whatever personal reason), you first have to apply a rubber moisture barrier sealant before the mastic glue. The sealant keeps the water from the mastic from reaching the dried compound.
With such a barrier backing, drying times of the mastic are increased. Keep your seams open and un-grouted until the mastic is fully dried and hard through and through.
An oil-based primer may also provide just enough of a moisture barrier -without separation- to prevent water penetration while the mastic is drying. (But do note that an oil-based primer does not suffice as a barrier in a shower.)

Image: https://rona.ca